<?php
class Controller_skusales extends Controller
{
    public function action_index()
    {
        if( PHP_SAPI !== 'cli' ) return;

        set_time_limit(0);                                                                                              //防超时
        ignore_user_abort(true);

        $count = 0;                                                                                                     //导入计数
        $maxDate = date('Y-m-d', $_SERVER['REQUEST_TIME']);
        $count_date = date('Y-m-d', $_SERVER['REQUEST_TIME'] - 86400);
        $maxRangeDate = date('Y-m-d', $_SERVER['REQUEST_TIME'] + 86400);
        $nowDate = date('Y-m-d H:i:s', $_SERVER['REQUEST_TIME']);
        echo "四大平台sku销量统计导入开始 : {$nowDate} \n";

        $sql = 'SELECT
            DATE_FORMAT(FROM_UNIXTIME(UNIX_TIMESTAMP(`add_time`) - 2505600), "%Y-%m-%d") maxDate
        FROM
            `sku_sales`
        ORDER BY
            `id` DESC 
        LIMIT 1';
        $minDate = DB::query(Database::SELECT, $sql)->execute('newerp')->as_array();                                    //读取数据库最大时间
        $minDate = empty($minDate) ? '1970-01-01' : $minDate[0]['maxDate'];                                             //生成最小范围的过滤条件

        $sql = date('Y-m-d', $_SERVER['REQUEST_TIME'] - 2592000);
        $sql = "DELETE FROM 
            `sku_sales`
        WHERE
            `add_time` < '{$sql}'";
        DB::query(Database::DELETE, $sql)->execute('newerp');

        foreach(array('ebay', 'smt', 'amz', 'wish') as $key => $tName) {                                                //四大平台表前缀
            $key += 1;                                                                                                  //平台标识
            $tName .= '_sales';                                                                                         //平台表名
            $sql = "SELECT
                upper (`sku`) `sku`,                                                  /*sku*/
                '{$key}' `stage`,                                       /*平台标识*/
                '{$count_date}' `count_date`,                                           /*数据日期*/
                '{$nowDate}' `add_time`,                                /*运算时间*/
                IFNULL(
                    TRUNCATE((SUM(IF(
                        DATEDIFF('{$maxDate}', `count_date`) < 8,
                        `sale`, 0
                    )) / 7),4) , 0
                ) daySale7,                                             /*7日平均销量*/
                IFNULL(
                    TRUNCATE((SUM(IF(
                        DATEDIFF('{$maxDate}', `count_date`) < 15,
                        `sale`, 0
                    )) / 14),4), 0
                ) daySale14,                                            /*14日平均销量*/
                IFNULL(
                    TRUNCATE((SUM(IF(
                        DATEDIFF('{$maxDate}', `count_date`) < 29,
                        `sale`, 0
                    )) / 28),4) , 0
                ) daySale28,                                            /*28日平均销量*/
                TRUNCATE(IFNULL(
                    SUM(IF(
                        DATEDIFF('{$maxDate}', `count_date`) < 8,
                        `sale`, 0
                    )) / 7 , 0
                ) * 0.5 + IFNULL(
                    SUM(IF(
                        DATEDIFF('{$maxDate}', `count_date`) < 15,
                        `sale`, 0
                    )) / 14 , 0
                ) * 0.3 + IFNULL(
                    SUM(IF(
                        DATEDIFF('{$maxDate}', `count_date`) < 29,
                        `sale`, 0
                    )) / 28 , 0
                ) * 0.2,4) daySale                                         /*预期销量*/
            FROM
                `{$tName}`
            WHERE
                `add_time` >= '{$minDate}'
            AND `add_time` < '{$maxRangeDate}'
            GROUP BY
                `sku`";

            $sql = "INSERT INTO `sku_sales` (
                `sku`, `stage`, `count_date`, `add_time`, `daysale7`, `daysale14`, `daysale28`, `daysale`
            ) {$sql}";

            DB::query(Database::INSERT, $sql)->execute('newerp');
        }

        $nowDate = time() - $_SERVER['REQUEST_TIME'];
        echo "导入操作结束 : 导入 {$count} 条, 耗时 {$nowDate} 秒\n";
    }
}